

local file_path_in "/tochange/"

insheet using "`file_path_in'/house price index/County_Zhvi_AllHomes.csv", names clear

rename regionname city

** create fips code to merge to corelogic
tostring statecodefips, gen(statefips)
tostring municipalcodefips, gen(cityfips)
replace statefips = "0"+statefips if strlen(statefips)==1
replace cityfips = "0"+cityfips if strlen(cityfips)==2 
replace cityfips = "00"+cityfips if strlen(cityfips)==1 

gen fips = statefips+cityfips
assert strlen(fips)==5
destring fips, replace

reshape long v, i(regionid city state fips statecodefips municipalcodefips) j(new)

gen year = 1996 if new==8

forval j = 1/21 {
	replace year = 1996+`j' if new==12*`j'+5
}

replace year  = year[_n-1] if mi(year)
bysort regionid year: gen month = _n
rename v houseprice
replace month = new -4 if new<=16

collapse (mean) houseprice, by(year month city state regionid fips statecodefips municipalcodefips) 

** create date variable
tostring year, replace
tostring month, replace
replace month = "0"+month if strlen(month)==1
gen temp0 = year+month+"01"
gen temp = date(temp0,"YMD")
format temp %td
gen date = mofd(temp)
format date  %tm
drop temp temp0
destring year, replace
destring month, replace

rename fips FIPS_CODE
drop statecodefips municipalcodefips regionid 
		
save "`file_path_in'/ZillowHpCounty.dta", replace
